<?php if(!defined('BASEPATH')) exit('No direct script access allowed!');
class Teachings_Model extends MY_Model{
	function __construct(){
		parent::__construct();
	}
	
	protected $_table_name = 'teachings';

	public function get_subject_id($person_id){
		$this->db->where('teacher_id',$person_id);
		$rs = $this->db->get($this->_table_name);
		$rs = $rs->result_array();
		return isset($rs[0]['subject_id'])?$rs[0]['subject_id']:0;
	}
	/*
	public function lists_class_array() {
		$ci = &get_instance();
		$data = $ci->classes->list_name();
		return $data;
	}
	*/
	public function lists_class_array($school_year_id, $teacher_id='', $subject_id='', $class_id=''){
		if(!empty($teacher_id)){
			$this->db->where('teacher_id',$teacher_id);
			$this->db->join('classes','classes.id = ' . $this->_table_name . '.class_id');
		}
		if(!empty($subject_id)){
			$this->db->where('subject_id',$subject_id);
		}
		if(!empty($class_id)){
			$this->db->where('class_id',$class_id);
		}
		
		$this->db->where('teachings.school_year_id',$school_year_id);
		$this->db->order_by('teacher_id');
		$this->db->order_by('subject_id');
		$this->db->order_by('class_id');
		//$this->db->group_by(array('teacher_id','subject_id'));
		$rs = $this->db->get($this->_table_name)->result_array();
		//echo $this->db->last_query();
		$data = array();
		$data[0] = '';
		foreach($rs as $row){
			$data[ $row['class_id'] ] = $row['name'];
		}
		return $data;
	}
	
	public function lists($school_year_id, $teacher_id='', $subject_id='', $class_id=''){
		if(!empty($teacher_id)){
			$this->db->where('teacher_id',$teacher_id);
		}
		if(!empty($subject_id)){
			$this->db->where('subject_id',$subject_id);
		}
		if(!empty($class_id)){
			$this->db->where('class_id',$class_id);
		}
		
		$this->db->where('school_year_id',$school_year_id);
		$this->db->order_by('teacher_id');
		$this->db->order_by('subject_id');
		$this->db->order_by('class_id');
		$this->db->group_by(array('teacher_id','subject_id'));
		$rs = $this->db->get($this->_table_name);		
		return $rs->result_array();
	}

	public function lists_without_group($school_year_id, $teacher_id='', $subject_id='', $class_id=''){
		if(!empty($teacher_id)){
			$this->db->where('teacher_id',$teacher_id);
		}
		if(!empty($subject_id)){
			$this->db->where('subject_id',$subject_id);
		}
		if(!empty($class_id)){
			$this->db->where('class_id',$class_id);
		}
		
		$this->db->where('school_year_id',$school_year_id);
		$this->db->order_by('teacher_id');
		$this->db->order_by('subject_id');
		$this->db->order_by('class_id');
		$rs = $this->db->get($this->_table_name);		
		return $rs->result_array();
	}
	
	public function get($school_year_id, $teaching_id=''){
		if(!empty($teaching_id)){
			$this->db->where('id',$teaching_id);
		}
		$this->db->where('school_year_id',$school_year_id);
		$rs = $this->db->get($this->_table_name);
		return $rs->row_array();
	}

	public function count($school_year_id, $teacher_id='',$subject_id=''){
		if(!empty($teacher_id)){
			$this->db->where('teacher_id',$teacher_id);
		}
		if(!empty($subject_id)){
			$this->db->where('subject_id',$subject_id);
		}
		$this->db->where('class_id !=','');
		$this->db->where('school_year_id',$school_year_id);
		$this->db->from($this->_table_name);
		return $this->db->count_all_results();
	}

	public function had_values($teaching_id){
		$value[0] = 'tests';
		$total = 0;
		for($i=0; $i<count($value); $i++){
			$this->db->where('id',$teaching_id);
				
			$this->db->from($value[$i]);
			$num = $this->db->count_all_results();
			//echo $num . " - ";
			$total += $num;
		}
		
		if( ($total) > 0)
			return TRUE;
		else
			return FALSE;
	}
	
	public function insert(&$input,$inserting_what){
		$school_year_id = $input->post('school_year_id');
		$teacher_id = $input->post('teacher_id');
		
		$data['school_year_id'] = $school_year_id;
		$data['teacher_id'] = $teacher_id;
		
		if($inserting_what=='subject'){
			//hapus semua bidang studi yang tidak memiliki kelas
			$this->delete_subject($school_year_id, $teacher_id);
			$checklist = $this->input->post('check_list');
			if(!empty($checklist))
				foreach ($checklist as $check) {
					if(!empty($check))
						$data['subject_id'] = $check;
					$this->db->insert($this->_table_name, $data);
				}
		}
		else{ //if inserting class
			$data['subject_id'] = $input->post('subject_id');
			
			//hapus semua kelas yang tidak memiliki nilai harian, tugas, psiko, afektif dan uas
			$teachings = $this->lists_without_group($school_year_id, $teacher_id, $data['subject_id']);
			foreach($teachings as $t){
				if ($this->had_values($t['id'])==0) {
					$this->delete($t['id']);
				}
			}
			
			$checklist = $this->input->post('check_list');
			if(!empty($checklist))
				foreach ($checklist as $check) {
					if(!empty($check)){
						$data['class_id'] = $check;
						$this->db->insert($this->_table_name, $data);
					}
				}
		}
	}
	
	//hanya menghapus data dalam table teachings, untuk class_id = NULL
	public function delete_subject($school_year_id, $teacher_id){
		$this->db->where('teacher_id', $teacher_id);
		$this->db->where('class_id IS NULL');
		$this->db->where('school_year_id', $school_year_id);
		$this->db->delete($this->_table_name);
	}
	
	public function delete($teaching_id){
		$this->db->where('id', $teaching_id);
		$this->db->delete($this->_table_name);
	}
		
	function get_teaching_list(){
		$this->db->select('s.id sid, s.name sname, c.id cid, c.name cname, t2.nip nip');
		$this->db->from('teachings t');
		$this->db->join('subjects s','s.id=t.subject_id','left');
		$this->db->join('classes c','t.class_id=c.id','left');
		$this->db->join('teachers t2','t.teacher_id=t2.id','left');
		$this->db->where('nip = '.$_SESSION['user']);
		return $this->db->get()->result();
	}	

	public function lists_by_teacher_id($school_year_id, $teacher_id, $subject_id='') {
		$sql = 
"SELECT t.*, c.name AS class_name, c.room_code AS class_room_code, s.name AS subject_name
	FROM teachings AS t
	INNER JOIN classes AS c ON c.id=t.class_id AND c.school_year_id=t.school_year_id
	INNER JOIN subjects AS s ON s.id=t.subject_id
WHERE t.school_year_id=$school_year_id AND t.teacher_id=$teacher_id";
		if (!empty($subject_id)) {
			$sql .= " AND t.subject_id=$subject_id";
		}
		$sql .= " ORDER BY s.name, c.name";
		$rs = $this->db->query($sql);
		return $rs->result_array();
	}

	public function lists_by_teacher_id_for_options($school_year_id, $teacher_id, $subject_id='') {
		$teachings = $this->lists_by_teacher_id($school_year_id, $teacher_id, $subject_id);
		$array = array();
		foreach ($teachings as  $t) {
			$class_name = explode(' ', $t['class_name']);
			$class_name = $class_name[0];
			if (!isset($array[$class_name])) {
				$array[$class_name] = array();
			}
			$array[$class_name][$t['class_id']] = "{$t['class_name']} ({$t['class_room_code']})";
		}
		return $array;
	}

	public function get_complete($teaching_id) {
		$sql = 
"SELECT t.*, c.name AS class_name, s.name AS subject_name
FROM teachings AS t
	INNER JOIN classes AS c ON c.id=t.class_id
	INNER JOIN subjects AS s ON s.id=t.subject_id
WHERE t.id=$teaching_id";
		$rs = $this->db->query($sql);
		return $rs->row_array();
	} 

	public function lists_subjects_and_its_teachings($school_year_id, $teacher_id) {
		$retval = array();

		$sql = 
"SELECT DISTINCT s.id, s.name
FROM teachings AS t
	INNER JOIN subjects AS s ON s.id=t.subject_id
WHERE t.school_year_id=$school_year_id AND t.teacher_id=$teacher_id
ORDER BY s.name";
		$rs = $this->db->query($sql);
		foreach ($rs->result_array() as $subject) {
			$sql = 
"SELECT t.id, c.name AS class_name, te.name AS official_teacher_name, tv.latest_test_order
FROM teachings AS t
	INNER JOIN classes AS c ON t.class_id=c.id AND t.school_year_id=c.school_year_id
	INNER JOIN officials AS o ON o.school_year_id=t.school_year_id AND o.class_id=c.id
	INNER JOIN teachers AS te ON te.id=o.teacher_id
	LEFT JOIN 
	(
		SELECT MAX(test_order) AS latest_test_order, teaching_id
		FROM test_values 
		GROUP BY teaching_id
	) AS tv ON t.id=tv.teaching_id
WHERE t.school_year_id=$school_year_id AND t.teacher_id=$teacher_id AND t.subject_id=".$subject['id'].
" ORDER BY c.name";
			$rs = $this->db->query($sql);
			$retval[] = $subject + array('teachings' => $rs->result_array());
		}
		return $retval;
	}

}
?>
